package cn.com.dashihui.api.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.api.base.BaseService;
import cn.com.dashihui.api.common.GroupCode;
import cn.com.dashihui.api.common.OrderCode;
import cn.com.dashihui.api.common.SerOrderCode;
import cn.com.dashihui.api.common.ServiceOrderCode;
import cn.com.dashihui.api.common.UserCode;
import cn.com.dashihui.api.dao.Bank;
import cn.com.dashihui.api.dao.Card;
import cn.com.dashihui.api.dao.History;
import cn.com.dashihui.api.dao.OrderG;
import cn.com.dashihui.api.dao.OrderZ;
import cn.com.dashihui.api.dao.User;
import cn.com.dashihui.api.dao.UserAddress;
import cn.com.dashihui.api.dao.UserCollection;
import cn.com.dashihui.kit.CommonKit;
import cn.com.dashihui.kit.DatetimeKit;
import cn.com.dashihui.kit.DoubleKit;
import net.sf.json.JSONObject;

public class UserService  extends BaseService{
	
	/**
	 * 根据sessionid查询对应登录用户信息，用于已经登录，只是查询用户基本信息返回给客户端，所以不再查询用户名及密码字段
	 * 注：需要加条件限制state=1，以限制用户不允许多客户端，目前没有加，2016-03-05
	 */
	public User findUserInfoBySessionid(String sessionid){
		return User.me().findFirst("SELECT"
				+ " u.*,cs.createDate as time,cs.state as sta,  "
//				+ "dc.id communityid,"
//				+ "dc.title communityName,"
//				+ "build.id buildid,"
//				+ "build.name buildName,"
//				+ "unit.id unitid,"
//				+ "unit.name unitName,"
//				+ "room.id roomid,"
//				+ "room.name roomName,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=u.id AND buc.isCancel=0)!=0 CollectedCount"
				+ " FROM t_bus_user u"
				+ " INNER JOIN t_api_client_session cs ON u.id=cs.userid"
//				+ " LEFT JOIN t_dict_community dc ON u.communityid=dc.id"
//				+ " LEFT JOIN t_dict_community_detail build ON u.buildid=build.id AND build.type=1"
//				+ " LEFT JOIN t_dict_community_detail unit ON u.unitid=unit.id AND unit.type=2"
//				+ " LEFT JOIN t_dict_community_detail room ON u.roomid=room.id AND room.type=3"
				+ " WHERE cs.sessionid=?",sessionid);
	}
	
	/**
	 * 查询指定用户名的用户信息，用于登录密码验证或验证指定用户名用户是否存在，所以只查询用户名和密码字段
	 */
	public User findByUsername(String username){
		return User.me().findFirst("SELECT id,username,password FROM t_bus_user WHERE username=?",username);
	}
	/**
	 * 根据手机号查询用户信息
	 */
	public User findByPhone(String phone){
		return User.me().findFirst("SELECT u.* FROM t_bus_user u WHERE u.phone=?",phone);
	}
	/**
	 * 查询指定ID的用户信息
	 */
	public User findUserInfoById(int userid){
		return User.me().findFirst("SELECT u.* FROM t_bus_user u WHERE u.id=?",userid);
	}
	
	/**
	 * 用户注册
	 */
	public boolean save(User user){
		//如果携带邀请人id 那么这个邀请人id必须有对应的注册用户
		if(user.getInt("inviteid")!=null){
			User user1 = User.me().findFirst("SELECT u.* FROM t_bus_user u WHERE u.id=?",user.getInt("inviteid"));
			if(user1 == null){
				return false;
			}
		}
		return user.save();
	}
	
	/**
	 * 用户登录
	 */
	@Before(Tx.class)
	public boolean login(String sessionid,int userid,String clientid){
//		Db.update("UPDATE t_api_client_session SET state=2 WHERE clientid=? OR userid=?",clientid,userid);
		Db.update("UPDATE t_api_client_session SET state=2 WHERE  userid=?",userid);
		Db.update("INSERT INTO t_api_client_session(sessionid,userid,clientid,state) VALUE(?,?,?,?)",sessionid,userid,clientid,1);
		Db.update("UPDATE t_bus_user SET state=3 WHERE id=?",userid);
		return true;
	}
	
	/**
	 * 统计用户各状态数量
	 * 1.统计用户收藏的商品数量
	 * 2.统计查询“待付款”的订单数量
	 * 3.统计查询“待发货”的订单数量
	 * 4.统计查询“待签收”的订单数量
	 * 5.统计服务订单“待付款”的订单数量
	 * 6.统计服务订单“待服务”的订单数量
	 */
	public Record getUserState(int userid){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.统计用户收藏的商品数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.isCancel=0) state1,");
		sqlParams.add(userid);
		
		//2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND payType=? AND payState=? AND orderState=?) state2,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//3.统计查询“待发货”的订单数量（1：“在线支付+送货上门”、“已支付”、“未发货”、“正常”，2：“货到付款+送货上门”、“未发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND takeType=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(payType=? AND payState=?)");
		sqlBuffer.append(" OR payType=?");
		sqlBuffer.append(") AND deliverState=? AND orderState=?) state3,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);
		sqlParams.add(OrderCode.OrderDeliverState.NO_DELIVER);sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//4.统计查询“待签收”的订单数量（1：“送货上门”、“已发货”、“正常”，2：“在线支付+门店自取”、“已支付”、“正常”，3：“货到付款+门店自取”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(takeType=? AND deliverState=?)");
		sqlBuffer.append(" OR (payType=? AND takeType=? AND payState=?)");
		sqlBuffer.append(" OR (payType=? AND takeType=?)");
		sqlBuffer.append(") AND orderState=?) state4,");
		sqlParams.add(userid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);sqlParams.add(OrderCode.OrderDeliverState.HAD_DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//5.统计查询“待付款”的服务订单数量+家政服务订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state5,");
		
		//6.统计查询“待服务”的服务订单数量+家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(ServiceOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state6");
		return Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
	}
	public Record getUserState_v131(int storeid,int userid){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.统计用户收藏的商品数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND (buc.storeid=? OR buc.isSelf=1) AND buc.isCancel=0) state1,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		
		//2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1) AND payType=? AND payState=? AND orderState=?) state2,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//3.统计查询“待发货”的订单数量（1：“在线支付+送货上门”、“已支付”、“未发货”、“正常”，2：“货到付款+送货上门”、“未发货”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1) AND takeType=?");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(payType=? AND payState=?)");
		sqlBuffer.append(" OR payType=?");
		sqlBuffer.append(") AND deliverState=? AND orderState=?) state3,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);
		sqlParams.add(OrderCode.OrderDeliverState.NO_DELIVER);sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//4.统计查询“待签收”的订单数量（1：“送货上门”、“已发货”、“正常”，2：“在线支付+门店自取”、“已支付”、“正常”，3：“货到付款+门店自取”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1)");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(takeType=? AND deliverState=?)");
		sqlBuffer.append(" OR (payType=? AND takeType=? AND payState=?)");
		sqlBuffer.append(" OR (payType=? AND takeType=?)");
		sqlBuffer.append(") AND orderState=?) state4,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderTakeType.DELIVER);sqlParams.add(OrderCode.OrderDeliverState.HAD_DELIVER);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);sqlParams.add(OrderCode.OrderTakeType.TAKE_SELF);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//5.统计查询“待付款”的服务订单数量+家政服务订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state5,");
		
		//6.统计查询“待服务”的服务订单数量+家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND storeid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(ServiceOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND storeid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state6");
		return Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
	}
	public Record getUserState_v132(int storeid,int userid){
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		List<Object> sqlParams = new ArrayList<Object>();
		//1.统计用户收藏的商品数量
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND (buc.storeid=? OR buc.isSelf=1) AND buc.isCancel=0) state1,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		
		//2.统计“待付款”的商品订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1) AND payType=? AND payState=? AND orderState=?) state2,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);
		sqlParams.add(OrderCode.OrderPayState.NO_PAY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//3.统计查询“待收/取货”的订单数量（1：“在线支付”、“已支付”、“正常”，2：“货到付款”、“正常”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1)");
		sqlBuffer.append(" AND (");
		sqlBuffer.append("(payType=? AND payState=?)");
		sqlBuffer.append(" OR payType=?");
		sqlBuffer.append(") AND orderState=?) state3,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderPayType.ON_LINE);sqlParams.add(OrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(OrderCode.OrderPayType.ON_DELIVERY);
		sqlParams.add(OrderCode.OrderState.NORMAL);
		
		//4.统计查询“待评价”的订单数量（1：“已完成”、“未评价”）
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_order WHERE userid=? AND (storeid=? OR isSelf=1) AND orderState=? AND evalState=0) state4,");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(OrderCode.OrderState.FINISH);
		
		//5.统计查询“待付款”的服务订单数量+家政服务订单数量（在线支付、未付款、正常）
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND storeid=? AND payType=? AND payState=? AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.NO_PAY);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state5,");
		
		//6.统计查询“待服务”的服务订单数量+家政服务订单数量（（在线支付、已付款、正常）或（服务后付款、正常））
		sqlBuffer.append("(");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_service_order WHERE userid=? AND storeid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(ServiceOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(ServiceOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(ServiceOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(ServiceOrderCode.OrderState.NORMAL);
		sqlBuffer.append("+");
		sqlBuffer.append("(SELECT COUNT(*) FROM t_bus_ser_order WHERE userid=? AND storeid=? AND ((payType=? AND payState=?) OR payType=?) AND orderState=?)");
		sqlParams.add(userid);
		sqlParams.add(storeid);
		sqlParams.add(SerOrderCode.OrderPayType.ON_LINE);
		sqlParams.add(SerOrderCode.OrderPayState.HAD_PAY);
		sqlParams.add(SerOrderCode.OrderPayType.AFTER_SERVICE);
		sqlParams.add(SerOrderCode.OrderState.NORMAL);
		sqlBuffer.append(") state6");
		return Db.findFirst(sqlBuffer.toString(),sqlParams.toArray());
	}
	
	/**
	 * 用户信息更新
	 */
	public boolean update(User user){
		return user.update();
	}
	/**
	 * 更新用户登录密码
	 */
	public boolean updatePwdByUsername(String password,String phone){
		String sql = "UPDATE t_bus_user SET password=? WHERE phone=?";
		return Db.update(sql, password, phone)==1;
	}
	
	/**
	 * 更新用户支付密码
	 */
	public boolean updatePayPwdByUsername(String password,String phone){
		String sql = "UPDATE t_bus_user SET paypassword=? WHERE phone=?";
		return Db.update(sql, password, phone)==1;
	}
	
	/**
	 * 用户收藏指定商品<br/>
	 * 因为收藏表中记录为了统计，所以用户取消收藏是不会将记录删除的，只是修改了标识isCancel为1<br/>
	 * 所以在此业务中需要判断同一用户同一商品是否已经存在，如果存在且是取消收藏状态，则只需要修改标识为0即可<br/>
	 * 如果不存在，则需要新插入记录<br/>
	 * 否则认为同一用户已经收藏过同一商品，直接返回true
	 */
	@Before(Tx.class)
	public void doCollect(int storeid,int userid,String[] goodsids,String[] isSelfs){
		//批量删除
		String delSql = "DELETE FROM t_bus_user_collection WHERE userid=? AND (storeid=? OR isSelf=1) AND goodsid=?";
		Object[][] params1 = new Object[goodsids.length][3];
		for(int i=0;i<goodsids.length;i++){
			params1[i][0] = userid;
			params1[i][1] = storeid;
			params1[i][2] = Integer.valueOf(goodsids[i]);
		}
		String addSql = "INSERT INTO t_bus_user_collection(userid,storeid,goodsid,isSelf) VALUES(?,?,?,?)";
		Db.batch(delSql, params1, goodsids.length);
		
		//批量插入
		Object[][] params2 = new Object[goodsids.length][4];
		for(int i=0;i<goodsids.length;i++){
			params2[i][0] = userid;
			params2[i][1] = storeid;
			params2[i][2] = Integer.valueOf(goodsids[i]);
			params2[i][3] = Integer.valueOf(isSelfs[i]);
		}
		Db.batch(addSql, params2, goodsids.length);
	}
	
	/**
	 * 用户取消收藏指定商品<br/>
	 */
	public void cancelCollect(int userid,String[] goodsids){
		String sql = "UPDATE t_bus_user_collection SET isCancel=1 WHERE userid=? AND goodsid=?";
		Object[][] params = new Object[goodsids.length][2];
		for(int index = 0;index<goodsids.length;index++){
			params[index][0] = userid;
			params[index][1] = goodsids[index];
		}
		Db.batch(sql, params, goodsids.length);
	}
	
	/**
	 * 分页查询用户收藏商品列表
	 * @param storeid 店铺ID
	 * @param categoryCode 主分类代码
	 * @param type 优惠类型
	 * @param pageNum 页码
	 * @param pageSize 数量
	 */
	public Page<Record> findCollectionByPage(int storeid,int userid,int pageNum,int pageSize){
		String sqlSelect = "SELECT coled.* ";
		String sqlExceptSelect = "FROM (SELECT bg.id,bg.name,bg.spec,bg.marketPrice,bg.sellPrice,bg.thumb,bg.type,bg.urv,bg.isSelf,uc.createDate collectDate"
					+ " FROM t_bus_user_collection uc"
					+ " INNER JOIN t_bus_goods bg ON uc.goodsid=bg.id"
					+ " WHERE uc.userid=? AND (uc.storeid=? OR uc.isSelf=1) AND uc.isCancel=0) coled ORDER BY coled.collectDate DESC";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlExceptSelect, userid, storeid);
	}
	
	/**
	 * 执行收藏操作
	 * @param userCollection
	 * @param fav
	 * @return
	 */
	
	public boolean setCollect(UserCollection userCollection,String fav,int goodsid,int userid ) {
		UserCollection userCollection1= userCollection.findFirst("select tbuc.* from t_bus_user_collection tbuc where tbuc.userid=? and goodsid=?", userid,goodsid);
		if ("false".equals(fav)) {
			if (userCollection1!=null) {
				userCollection1.set("isCancel", 0);
				return userCollection1.update();
			}else {
				return userCollection.save();
			}
			
		}else {
			userCollection1.set("isCancel", 1);
			return userCollection1.update();
		}
	}
	
	/**
	 * 查询用户收藏商品列表
	 */
	public List<Record> findCollection(int userid,String goodsid){
		if(StrKit.isBlank(goodsid)){
			String sqlSelect = "SELECT coled.* FROM (SELECT bg.id,bg.name,bg.spec,bg.sellPrice,bg.thumb,bg.urv,bgs.goodsSpec,bgs.saleprice,bgs.salestock,uc.createDate collectDate"
					+ " FROM t_bus_user_collection uc"
					+ " INNER JOIN t_bus_goods bg ON uc.goodsid=bg.id"
					+ " INNER JOIN t_bus_goods_sale bgs ON uc.goodsid=bgs.goodsid and bgs.id in (select min(bgs.id) from t_bus_user_collection uc  INNER JOIN t_bus_goods_sale bgs ON  bgs.goodsid=uc.goodsid group by uc.goodsid)"
					+ " WHERE uc.userid=? AND uc.isCancel=0) coled ORDER BY coled.collectDate DESC";
			return Db.find(sqlSelect, userid);
		}else {
			String sqlSelect = "SELECT *  FROM t_bus_user_collection  WHERE userid=? AND goodsid= ?";
			return Db.find(sqlSelect, userid,Integer.valueOf(goodsid));
		}
		
	}
	
	/**
	 * 查询用户收藏数量
	 */
	public Record countCollectionList(int userid) {
		return Db.findFirst("SELECT COUNT(*) AS collectionNum FROM t_bus_user_collection buc WHERE buc.userid=? AND  buc.isCancel=0", userid);
	}
	
	/**
	 * 查询用户收货地址列表
	 * @param userid 用户ID
	 */
	public List<UserAddress> findAddress(int userid){
		return UserAddress.me().find("SELECT ua.id,ua.userid,ua.linkName,ua.tel,"
				+ "ua.address,"
				+ "ua.isDefault"
				+ " FROM t_bus_user_address ua"
				+ " WHERE ua.userid=?",userid);
	}
	
	/**
	 * 添加收货地址<br/>
	 * 1.如果本次添加的收货地址，要求设置为默认，则更新该用户的其他收货地址为非默认（在此不再查询用户是否还有其他收货地址，直接UPDATE）
	 * 2.如果本次添加的收货地址，没有要求设置为默认，则查询该用户是否还有其他收货地址，如果没有，则同样设置本次为默认（因为只有一条收货地址时，应当设置为默认）
	 */
	@Before(Tx.class)
	public boolean addAddress(int userid,UserAddress address,int isDefault){
		address.set("isDefault", 0);
		if(isDefault==1){
			Db.update("UPDATE t_bus_user_address SET isDefault=0 WHERE userid=?",userid);
			address.set("isDefault", 1);
		}else{
			Record addrCount = Db.findFirst("SELECT count(*) total FROM t_bus_user_address WHERE userid=?",userid);
			if(addrCount.getLong("total").intValue()==0){
				address.set("isDefault", 1);
			}
		}
		return address.save();
	}
	 
	/*
	 * 查询支持银行bankNoID表ID
	 */
	public Record queryBank(String bankName) {
		return Db.findFirst("select id from t_bus_user_bankno  where bankName=?",bankName);
	}
	
	/*
	 * 查询支持银行bankNoID表
	 */
	public List<Record> queryBankAll() {
		return Db.find("select bankId,bankName from t_bus_user_bankid");
	}
	/**
	 * 根据银行编码查询支持银行名称bankNoID表
	 * @param bankId
	 * @return
	 */
	public Record queryBankNameByid(int bankId) {
		return Db.findFirst("select bankName from t_bus_user_bankid where bankId=?", bankId);
	}
	
	/**
	 * 新增卡信息
	 * @param userid
	 * @param card
	 * @param isDefault
	 * @return
	 */
	@Before(Tx.class)
	public boolean addCard(int userid, Card card, Integer isDefault) {
		card.set("isDefault", 0);
		//如果新增为默认，那么把数据库所有设置为非默认，在添加
		if(isDefault==1){
			Db.update("UPDATE t_bus_user_card SET isDefault=0 WHERE userid=?",userid);
			card.set("isDefault", 1);
		}else{
			//如果新增不是默认，但是该用户在数据没有相关卡信息，那么新增为默认
			Record cardCount = Db.findFirst("SELECT count(*) total FROM t_bus_user_card WHERE userid=?",userid);
			if(cardCount.getLong("total").intValue()==0){
				card.set("isDefault", 1);
			}
		}
		return card.save();
	}
	/**
	 * 
	 * @param userid
	 * @param cardid
	 * @return
	 */
	@Before(Tx.class)
	public int setDefault(int userid, String cardid) {
		Db.update("UPDATE t_bus_user_card SET isDefault=0 WHERE userid=?",userid);
		return Db.update("UPDATE t_bus_user_card SET isDefault=1 WHERE userid=? AND id=?",userid,cardid);
	}
	
	/**
	 * 解绑卡片    TODO   目前解绑接口没接通  所以先做删除
	 */
	@Before(Tx.class)
	public boolean delcard(int userid ,String cardid,String isDefault){
		boolean flag = Card.me().deleteById(cardid);
		Integer defaul = Integer.valueOf(isDefault);
		if(defaul == 1){
			Record card = Db.findFirst("SELECT id FROM t_bus_user_card WHERE userid=?", userid);
			if(card != null ){
				Db.update("UPDATE t_bus_user_card SET isDefault=1 WHERE userid=? AND id=?",userid,card.getInt("id"));
			}
		}
		return flag;
		
	}
	/**
	 * 查询所有绑定卡片   
	 */
	public List<Record> queryCard(int userid){
		String sql = "SELECT c.*,b.bankName "
				+ "FROM t_bus_user_card c "
				+ "INNER JOIN t_bus_user_bank b "
				+ "ON c.bankid = b.id WHERE c.userid = ?";
		return Db.find(sql, userid);
	}
	/**
	 * 查找指定用户的默认绑定卡片   
	 */
	public Record findDefaultCardByUser(int userid){
		return Db.findFirst("SELECT ua.id,ua.custName,ua.phoneNo,"
				+ "ua.cardNo,ua.idNo,ua.cardType,b.bankName,"
				+ "ua.bankid "
				+ " FROM t_bus_user_card ua "
				+ " INNER JOIN t_bus_user_bank b "
				+ " ON ua.bankid = b.id "
				+ " WHERE 1=1 AND ua.isDefault=1 AND ua.userid=?",userid);
	}
	/*
	 * 根据bankCode查找bankName
	 */
//	public Bank findNameByCode(String bankCode){
//		return Bank.me().findFirst("SELECT ua.bankName"
//				+ " FROM t_bus_user_bank ua"
//				+ " WHERE ua.bankCode=?",bankCode);
//	}
	/**
	 * 删除收货地址
	 */
	public void delAddress(int userid,String[] addressids){
		String sql = "DELETE FROM t_bus_user_address WHERE userid=? AND id=?";
		Object[][] params = new Object[addressids.length][2];
		for(int index = 0;index<addressids.length;index++){
			params[index][0] = userid;
			params[index][1] = addressids[index];
		}
		Db.batch(sql, params, addressids.length);
	}
	
	/**
	 * 修改收货地址<br/>
	 * 如果本次添加的收货地址，要求设置为默认，则更新该用户的其他收货地址为非默认（在此不再查询用户是否还有其他收货地址，直接UPDATE）
	 */
	@Before(Tx.class)
	public boolean updateAddress(int userid,UserAddress address,int isDefault){
		address.set("isDefault", 0);
		if(isDefault==1){
			Db.update("UPDATE t_bus_user_address SET isDefault=0 WHERE userid=?",userid);
			address.set("isDefault", 1);
		}
		return address.update();
	}
	
	/**
	 * 查找指定ID的收货地址信息
	 */
	public UserAddress findAddressById(int addressid){
		return UserAddress.me().findFirst("SELECT ua.id,ua.linkName,ua.sex,ua.tel,"
				+ "ua.storeid,"
				+ "store.title storeTitle,"
				+ "ua.address,"
				+ "ua.isDefault"
				+ " FROM t_bus_user_address ua"
				+ " LEFT JOIN t_dict_store store ON ua.storeid=store.id"
				+ " WHERE ua.id=?",addressid);
	}
	
	
	/**
	 * 查找指定用户的默认收货地址信息
	 */
	public UserAddress findDefaultAddressByUser(int userid){
		return UserAddress.me().findFirst("SELECT ua.id,ua.linkName,ua.tel,"
				+ "ua.address,"
				+ "ua.isDefault"
				+ " FROM t_bus_user_address ua"
				+ " WHERE ua.isDefault=1 AND ua.userid=?",userid);
	}
	
	/**
	 * 查询用户服务订单列表，包含家政类订单及其他类订单
	 * @param storeid 店铺ID
	 * @param userid 用户ID
	 * @param flag 0：全部，1：待付款，2：待服务，3：已完成订单
	 */
	@Before(Tx.class)
	public Page<Record> findServiceOrderByPage(int storeid,int userid,int flag,int pageNum,int pageSize){
		StringBuffer sqlBuffer = new StringBuffer("FROM (");
		//根据不同查询条件，拼上不同的状态条件
		if(flag==1){
			//1:待付款（“在线支付”、“未支付”、“正常”）
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("sjo.storeid,sjo.orderNum,1 type,sjo.userid,(CASE WHEN sjo.type=1 THEN CONCAT('日常保洁',title) ELSE CONCAT('深度保洁',title) END) serviceTitle,");
			sqlBuffer.append("sjo.payType,sjo.payState,sjo.deliverState,sjo.orderState,sjo.serTime,sjo.address,sjo.proSerName,sjo.amount,sjo.startDate,sjo.createDate");
			sqlBuffer.append(" FROM t_bus_ser_order sjo");
			sqlBuffer.append(" WHERE sjo.storeid=").append(storeid);
			sqlBuffer.append(" AND sjo.orderState=").append(SerOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" AND sjo.payType=").append(SerOrderCode.OrderPayType.ON_LINE);
			sqlBuffer.append(" AND sjo.payState=").append(SerOrderCode.OrderPayState.NO_PAY);
			sqlBuffer.append(" UNION ");
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("soo.storeid,soo.orderNum,2 type,soo.userid,sool.serviceTitle,soo.payType,soo.payState,soo.deliverState,soo.orderState,soo.serTime,soo.address,sool.shopName proSerName,soo.amount,soo.startDate,soo.createDate");
			sqlBuffer.append(" FROM t_bus_service_order soo");
			sqlBuffer.append(" INNER JOIN t_bus_service_order_list sool ON soo.orderNum=sool.orderNum");
			sqlBuffer.append(" WHERE soo.storeid=").append(storeid);
			sqlBuffer.append(" AND soo.orderState=").append(ServiceOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" AND soo.payType=").append(ServiceOrderCode.OrderPayType.ON_LINE);
			sqlBuffer.append(" AND soo.payState=").append(ServiceOrderCode.OrderPayState.NO_PAY);
		}else if(flag==2){
			//2：待服务（1：“在线支付”、“已支付”、“正常”，2：“服务后付款”、“正常”）
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("sjo.storeid,sjo.orderNum,1 type,sjo.userid,(CASE WHEN sjo.type=1 THEN CONCAT('日常保洁',title) ELSE CONCAT('深度保洁',title) END) serviceTitle,");
			sqlBuffer.append("sjo.payType,sjo.payState,sjo.deliverState,sjo.orderState,sjo.serTime,sjo.address,sjo.proSerName,sjo.amount,sjo.startDate,sjo.createDate");
			sqlBuffer.append(" FROM t_bus_ser_order sjo");
			sqlBuffer.append(" WHERE sjo.storeid=").append(storeid);
			sqlBuffer.append(" AND sjo.orderState=").append(SerOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" AND ((sjo.payType=").append(SerOrderCode.OrderPayType.ON_LINE);
			sqlBuffer.append(" AND sjo.payState=").append(SerOrderCode.OrderPayState.HAD_PAY).append(")");
			sqlBuffer.append(" OR sjo.payType=").append(SerOrderCode.OrderPayType.AFTER_SERVICE).append(")");
			sqlBuffer.append(" UNION ");
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("soo.storeid,soo.orderNum,2 type,soo.userid,sool.serviceTitle,soo.payType,soo.payState,soo.deliverState,soo.orderState,soo.serTime,soo.address,sool.shopName proSerName,soo.amount,soo.startDate,soo.createDate");
			sqlBuffer.append(" FROM t_bus_service_order soo");
			sqlBuffer.append(" INNER JOIN t_bus_service_order_list sool ON soo.orderNum=sool.orderNum");
			sqlBuffer.append(" WHERE soo.storeid=").append(storeid);
			sqlBuffer.append(" AND soo.orderState=").append(ServiceOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" AND ((soo.payType=").append(ServiceOrderCode.OrderPayType.ON_LINE);
			sqlBuffer.append(" AND soo.payState=").append(ServiceOrderCode.OrderPayState.HAD_PAY).append(")");
			sqlBuffer.append(" OR soo.payType=").append(ServiceOrderCode.OrderPayType.AFTER_SERVICE).append(")");
		}else if(flag==3){
			//3：已完成（“已完成”）
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("sjo.storeid,sjo.orderNum,1 type,sjo.userid,(CASE WHEN sjo.type=1 THEN CONCAT('日常保洁',title) ELSE CONCAT('深度保洁',title) END) serviceTitle,");
			sqlBuffer.append("sjo.payType,sjo.payState,sjo.deliverState,sjo.orderState,sjo.serTime,sjo.address,sjo.proSerName,sjo.amount,sjo.startDate,sjo.createDate");
			sqlBuffer.append(" FROM t_bus_ser_order sjo");
			sqlBuffer.append(" WHERE sjo.storeid=").append(storeid);
			sqlBuffer.append(" AND sjo.orderState=").append(SerOrderCode.OrderState.FINISH);
			sqlBuffer.append(" UNION ");
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("soo.storeid,soo.orderNum,2 type,soo.userid,sool.serviceTitle,soo.payType,soo.payState,soo.deliverState,soo.orderState,soo.serTime,soo.address,sool.shopName proSerName,soo.amount,soo.startDate,soo.createDate");
			sqlBuffer.append(" FROM t_bus_service_order soo");
			sqlBuffer.append(" INNER JOIN t_bus_service_order_list sool ON soo.orderNum=sool.orderNum");
			sqlBuffer.append(" WHERE soo.storeid=").append(storeid);
			sqlBuffer.append(" AND soo.orderState=").append(ServiceOrderCode.OrderState.FINISH);
		}else{
			//0：全部，不显示“删除”、“过期”的订单
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("sjo.storeid,sjo.orderNum,1 type,sjo.userid,(CASE WHEN sjo.type=1 THEN CONCAT('日常保洁',title) ELSE CONCAT('深度保洁',title) END) serviceTitle,");
			sqlBuffer.append("sjo.payType,sjo.payState,sjo.deliverState,sjo.orderState,sjo.serTime,sjo.address,sjo.proSerName,sjo.amount,sjo.startDate,sjo.createDate");
			sqlBuffer.append(" FROM t_bus_ser_order sjo");
			sqlBuffer.append(" WHERE sjo.storeid=").append(storeid);
			sqlBuffer.append(" AND (sjo.orderState=").append(SerOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" OR sjo.orderState=").append(SerOrderCode.OrderState.FINISH);
			sqlBuffer.append(" OR sjo.orderState=").append(SerOrderCode.OrderState.CANCEL);
			sqlBuffer.append(") UNION ");
			sqlBuffer.append("SELECT ");
			sqlBuffer.append("soo.storeid,soo.orderNum,2 type,soo.userid,sool.serviceTitle,soo.payType,soo.payState,soo.deliverState,soo.orderState,soo.serTime,soo.address,sool.shopName proSerName,soo.amount,soo.startDate,soo.createDate");
			sqlBuffer.append(" FROM t_bus_service_order soo");
			sqlBuffer.append(" INNER JOIN t_bus_service_order_list sool ON soo.orderNum=sool.orderNum");
			sqlBuffer.append(" WHERE soo.storeid=").append(storeid);
			sqlBuffer.append(" AND (soo.orderState=").append(ServiceOrderCode.OrderState.NORMAL);
			sqlBuffer.append(" OR soo.orderState=").append(ServiceOrderCode.OrderState.FINISH);
			sqlBuffer.append(" OR soo.orderState=").append(ServiceOrderCode.OrderState.CANCEL);
			sqlBuffer.append(")");
		}
		sqlBuffer.append(")");
		sqlBuffer.append(" v INNER JOIN t_dict_store ds ON v.storeid=ds.id WHERE v.userid=? ORDER BY createDate DESC");
		return Db.paginate(pageNum, pageSize, "SELECT v.*,ds.title storeName ",sqlBuffer.toString(),userid);
	}
	
	/**
	 * 查询用户实惠币变动记录
	 * @param pageNum
	 * @param pageSize
	 * @param userid
	 * @param searchType 1:我的购物收益明细; 2:好友购物收益明细; 3:我的消费记录; 4:好友购物收益合计
	 * @return
	 */
	public Page<Record> findUserExpenseRecord(int pageNum, int pageSize, int userid,int searchType){
		String sqlSelect = "SELECT buvl.* ";
		StringBuffer sqlExceptSelect = new StringBuffer("FROM t_bus_user_value_log buvl ");
		if(searchType == 1){
			sqlExceptSelect.append("WHERE buvl.userid=? AND buvl.flag=1 ");
			sqlExceptSelect.append("ORDER BY createDate DESC");
		} else if(searchType == 2){
			sqlSelect = "SELECT buvl.*,bu.nickName";
			sqlExceptSelect.append("LEFT JOIN t_bus_user bu ON buvl.fromUserid = bu.id ");
			sqlExceptSelect.append("WHERE buvl.userid=? AND buvl.flag=2 ");
			sqlExceptSelect.append("ORDER BY createDate DESC");
		} else if(searchType == 3){
			sqlExceptSelect.append("WHERE buvl.userid=? AND buvl.flag=3 ");
			sqlExceptSelect.append("ORDER BY createDate DESC");
		} else if(searchType == 4){
			sqlSelect = "SELECT g.nickName,SUM(g.amount) amount,g.fromUserid,g.createDate ";
			sqlExceptSelect.delete(0,sqlExceptSelect.length());
			sqlExceptSelect.append("FROM ( SELECT buvl.id,buvl.userid,buvl.flag,buvl.amount,buvl.fromUserid,buvl.createDate,bu.nickName ");
			sqlExceptSelect.append("FROM t_bus_user_value_log buvl INNER JOIN t_bus_user bu ");
			sqlExceptSelect.append("ON buvl.fromUserid = bu.id ");
			sqlExceptSelect.append("WHERE bu.inviteUserid = ? AND buvl.flag = 2 ) g ");
			sqlExceptSelect.append("GROUP BY g.nickName,g.fromUserid,g.createDate ");
			sqlExceptSelect.append("ORDER BY g.createDate DESC");
		}
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlExceptSelect.toString(), userid);
	}
	
	/**
	 * 查询用户推荐的好友的数量
	 * @param userid
	 * @return
	 */
	public Record findInviteUserCount(int userid){
		String sql = "SELECT count(1) AS inviteCount FROM t_bus_user WHERE inviteUserid = ?";
		return Db.findFirst(sql,userid);
	}
	/**
	 * 根据bankName查询bankid
	 * @param bankName
	 * @return
	 */
	public Bank findBankidByBankName(String bankCode) {
		String sql = "SELECT id FROM t_bus_user_bank WHERE bankName=?";
		return Bank.me().findFirst(sql, bankCode);
	}

	public Card findCardByCardNo(int userid, String cardNo) {
		String sql = "SELECT id FROM t_bus_user_card WHERE userid=? AND cardNo=?";
		return Card.me().findFirst(sql, userid,cardNo);
	}
/**
 * 查找子ID
 */
	public List<Record> findByInviteidOne(int id) {
		String sql = "SELECT tu.name,tu.phone,tu.taici,tu.inviteid,tu.id,tu.avator,tu.createDate "
				+ "FROM t_bus_user tu "
				+ "WHERE inviteid = ? order by createDate DESC";
		return Db.find(sql,id);
	}

	/**
	 * 根据邀请码查找所属公司
	 * @param inviteid
	 */
	public User findUserByInviteid(int inviteid) {
		return User.me().findFirst("SELECT toBossThreeId,toBossTwoId,toBossOneId,id,sppoint FROM t_bus_user WHERE id=?",inviteid);
	}

	/**
	 * 查找是否是首次登录资金
	 * @param id
	 */
	public User findFirst(Integer id) {
		return User.me().findFirst("SELECT isUpdate FROM t_bus_user WHERE id=?",id);
		
	}
	/**
	 * 修改用户首次登录修改支付密码
	 * @param id
	 * @return 
	 */
	public int updateUserFirstPayLoginById(Integer id) {
		return Db.update("UPDATE t_bus_user SET isUpdate=1 WHERE id=?", id);
	}
	
	/**
	 * 每次查询余额，更新用户余额
	 * @return 
	 */
	public int saveMoneyUp(Double money ,int id) {
		return Db.update("UPDATE t_bus_user SET money=? WHERE id=?", money,id);
	}
	
	
	/**
	 * 改变用户金额
	 * @param user
	 * @param amount
	 * @return 1  成功  2  更改用户余额失败  3记录money变化表失败
	 * @throws Exception 
	 */
	public int chanegMoney(User user,Double moeny) throws Exception {
		int userid = user.getInt("id");
		//变化前金额
		Double oldMoney = user.getDouble("money");
		String destype = null;
		Double addMoney = 0.00;
		if(Double.compare(oldMoney, moeny)>0){
			//提现
			//减少的金额
			addMoney = DoubleKit.sub(oldMoney, moeny);
			destype = "1";
		}else if(Double.compare(oldMoney, moeny)<0){
			//入金
			//增加的金额
			addMoney = DoubleKit.sub(moeny, oldMoney);
			destype = "3";
		}else{
			//无变化
			return -1;
		}
		//变化后金额
		Double newMoney = moeny;
		if(Db.update("UPDATE t_bus_user SET money = ? WHERE id = ?", newMoney,userid)!=1){
			throw new Exception("更改用户余额失败");
		}
		return 1;
	}

	
	/**
	 * 根据userid 查询
	 * @param id
	 */
	public Record queryInviteByid(Integer id) {
		return Db.findFirst("SELECT COUNT(id) as count FROM t_bus_user WHERE inviteid=?",id);
	}
	/**
	 * 查询邀请人数排行榜
	 * @param Spoint
	 * @return 
	 */
	public List<Record> queryInvite(Double Spoint) {
		List<Record> list = Db.find("SELECT u.id,u.name,u.phone,u.inviteid,u.avator FROM t_bus_user u ");
		if(list.size()>0&&list!=null){
			for(Record re : list){
				int inid = re.getInt("id");
				Record res = queryInviteByid(inid);
				Long count = res.getLong("count");
				Double SpointSum = DoubleKit.mul(count, Spoint);
				re.set("SpointSum", SpointSum);
			}
		}
		return list;
	}
	/**
	 * 更换绑定手机号
	 * @param int1
	 */
	public int updatePhoneByid(String phone,Integer id,String payid) {
		return Db.update("UPDATE t_bus_user SET phone=?,payid=? WHERE id=?", phone,payid,id);
	}
	
	/**
	 * 更改用户积分并记录
	 * @param return 1 成功  2保存积分日志表失败 3 更改用户积分失败
	 * @throws Exception 
	 */
	@Before(Tx.class)
	public int updateSpoint(User user,Double spoint,Record re) throws Exception {
		//更改前的积分
		Double oldspoint = user.getDouble("sppoint");
		Double deduHistory = user.getDouble("jkpoint");
		Double couponHistory = user.getDouble("jypoint");
		Double ecouponHistory = user.getDouble("ecoupon");
		//更改后
		Double newSpoint = DoubleKit.add(oldspoint, spoint);
		user.set("sppoint", newSpoint);
		if(user.update()){
			re.set("createDate", new Date())
			.set("sppoint", spoint)
			.set("sppointHistory", oldspoint)
			.set("deduHistory", deduHistory)
			.set("couponHistory", couponHistory)
			.set("ecouponHistory", ecouponHistory);
			if(!Db.save("t_bus_user_value_log", re)){
				throw new Exception("保存积分日志表失败");
			}
			return 1;
		}
		throw new Exception("更改用户积分失败");
	}
	/**
	 * 查询订单信息
	 * @param int1
	 * @return 
	 */
	public List<Record> findPayOrderIdByuserid(Integer id) {
		String sql = "SELECT tp.*,tu.money as oldmoney FROM t_bus_user_pay_orderid tp "
				+ "LEFT JOIN t_bus_user tu ON tu.id=tp.userid "
				+ "WHERE tp.userid=? AND tp.ischeck=? ";
		return Db.find(sql,id,0);
	}

	/**
	 * 查询状态
	 * @param jsondata
	 * return 1成功  2保存t_bus_user_pay_moeny失败   3 更改t_bus_user_pay_orderid失败
	 * @throws Exception 
	 * 
	 */
	@Before(Tx.class)
	public int logPay(JSONObject jsondata,int id,Record re) throws Exception {
		
		//支付成功   记录
		if(jsondata.get("order_type").equals("2")&&jsondata.get("order_status").equals("S")){
			Double oldmoney = re.getDouble("oldmoney");
			Double newmoney = DoubleKit.sub(oldmoney,Double.parseDouble(jsondata.get("order_money").toString()) );
			if(Db.update("UPDATE t_bus_user SET money=? WHERE id=?",newmoney, id)!=1){
				throw new Exception("更改t_bus_user失败");
			}
			Record res = new Record().set("userid", id)
					.set("type", 2)				
					.set("amount", jsondata.get("order_money"))
					.set("createDate", new Date())
					.set("describe", "购物消费")
					.set("orderid", re.getStr("orderid"))
					.set("orderType",OrderCode.MoneyState.GOODORDER)
					.set("nowAmount",newmoney)
					.set("oldAmount",oldmoney)
					.set("fromOrderNum", re.get("fromOrderNum"));
			
			if(!Db.save("t_bus_user_money", res)){
				throw new Exception("保存t_bus_user_pay_moeny失败");
			}
			//更改状态
			if(Db.update("UPDATE t_bus_user_pay_orderid SET ischeck =?,updatetime=? WHERE orderid=?", 1,new Date(),jsondata.get("order_id"))!=1){
				throw new Exception("更改t_bus_user_pay_orderid失败");
			}
			return 1;
		//如果支付失败  记录
		}else if(jsondata.get("order_type").equals("2")&&jsondata.get("order_status").equals("F")){
			//更改状态
			re.set("ischeck", "2");
			if(Db.update("UPDATE t_bus_user_pay_orderid SET ischeck =?,`updatetime`=? WHERE orderid=?", 2,new Date(),jsondata.get("order_id"))!=1){
				throw new Exception("更改t_bus_user_pay_orderid失败");
			}
		//支付
		}else if(jsondata.get("order_type").equals("2")&&jsondata.get("order_status").equals("F1")){
			//更改状态
			re.set("ischeck", "2");
			if(Db.update("UPDATE t_bus_user_pay_orderid SET ischeck =?,`updatetime`=? WHERE orderid=?", 2,new Date(),jsondata.get("order_id"))!=1){
				throw new Exception("更改t_bus_user_pay_orderid失败");
			}
		}else if(jsondata.get("order_status").equals("FF")){
			
			//更改状态
			re.set("ischeck", 2);
			re.set("updatetime",Timestamp.valueOf(DatetimeKit.getFormatDate("yyyy-MM-dd HH:mm:ss")));
			String sql = "UPDATE t_bus_user_pay_orderid SET ischeck=2,updatetime=? WHERE orderid=?";
			if(Db.update(sql, re.getTimestamp("updatetime"),re.getStr("orderid"))!=1){
				throw new Exception("更改t_bus_user_pay_orderid失败");
			}
		}
		
		return 10;
	}
	
	/**
	 * 更改用户积分,抵扣券，惠券并记录
	 * @param user 用户
	 * @param spoint 本次变动积分
	 * @param dedu 本次变动抵扣券
	 * @param coupon 本次变动惠券
	 * @param ecoupon 本次变动电子券
	 * @param money 本次变动金额
	 * @param re 本次记录
	 * @return1 成功  2保存财富日志表失败 3 更改用户财富失败
	 * @throws Exception
	 */
	@Before(Tx.class)
	public int updateValue(User user,Double spoint,Double dedu,Double coupon,Double ecoupon,Double money, Record re,int type) throws Exception {
		//更改前的值
		Double sppointHistory = user.getDouble("sppoint");
		Double deduHistory = user.getDouble("jkpoint");
		Double couponHistory = user.getDouble("jypoint");
		Double ecouponHistory = user.getDouble("ecoupon");
		//更改后
		Double newSpoint = 0.00;
		Double newJkpoint = 0.00;
		Double newJypoint = 0.00;
		Double newEcoupon = 0.00;
		if (type==1) {
			newSpoint = DoubleKit.add(sppointHistory, spoint);
			newJkpoint = DoubleKit.add(deduHistory, dedu);
			newJypoint = DoubleKit.add(couponHistory, coupon);
			newEcoupon = DoubleKit.add(ecouponHistory, ecoupon);
		}else {
			newSpoint = DoubleKit.sub(sppointHistory, spoint);
			newJkpoint = DoubleKit.sub(deduHistory, dedu);
			newJypoint = DoubleKit.sub(couponHistory, coupon);
			newEcoupon = DoubleKit.add(ecouponHistory, ecoupon);
		}
		
		//修改
		user.set("sppoint", newSpoint);
		user.set("jkpoint", newJkpoint);
		user.set("jypoint", newJypoint);
		user.set("ecoupon", newEcoupon);
		if(user.update()){
			
			//记录
			re.set("createDate", new Date())
			.set("sppoint", spoint)
			.set("sppointHistory", sppointHistory)
			.set("dedu", dedu)
			.set("deduHistory", deduHistory)
			.set("coupon", coupon)
			.set("couponHistory", couponHistory)
			.set("ecoupon", ecoupon)
			.set("ecouponHistory", ecouponHistory)
			.set("orderMoney", money);
			if(!Db.save("t_bus_user_value_log", re)){
				throw new Exception("保存财富日志表失败");
			}
			return 1;
		}
		throw new Exception("更改用户财富失败");
	}
	
	/**
	 * 评价表
	 * @param record
	 * @return 
	 * @throws Exception 
	 */
	@Before(Tx.class)
	public int addEval(Record record,String type) throws Exception {
		//保存评价
		if(!Db.save("t_bus_order_eval", record)){
			throw new Exception("保存评价失败");
		}
		String sql = null;
		if("G".equals(type)){
			//普通订单
			sql = "UPDATE t_bus_order SET orderState=4 WHERE orderNum=? AND userid=?";
		}else if("D".equals(type)){
			//兑换订单
			sql = "UPDATE t_bus_order_act SET orderState=4 WHERE orderNum=? AND userid=?";
		}else {
			//兑换订单
			sql = "UPDATE t_bus_order_gb SET orderState=4 WHERE orderNum=? AND userid=?";
		}
		//更改订单状态
		
		Integer id = record.getInt("userid");
		if(Db.update(sql,record.getStr("orderNum"),id)!=1 ){
			throw new Exception("更改订单状态失败");
		}
		return 1;
	}
	/**
	 * 存储轮播图
	 */
	public boolean saveImageByLunBoNum(Record re) {
		return Db.save("t_bus_order_eval_images", re);
	}
	
	/**
	 * 添加商品浏览记录
	 * @param history
	 * @return
	 */
	public boolean addHistory(History history) {
		return history.save();
	}
	/**
	 * 修改商品的浏览记录
	 * @param history
	 * @return
	 */
	public boolean updateHistory(History history) {
		return history.update();
	}
	
	/**
	 * 删除商品的浏览记录
	 * @param historyId
	 * @return
	 */
	public boolean deleHistory(int historyId) {
		return Db.deleteById("t_bus_user_history", historyId);
	}
	
	/**
	 * 查询单条浏览记录
	 * @param userId
	 * @param goodsId
	 * @param goodsType
	 * @return
	 */
	public History queryHistory(int userId,int goodsId,int goodsType) {
		String sqlSelect = "select * from t_bus_user_history where userId=? and goodsId=? and goodsType=?";
		return History.me().findFirst(sqlSelect, userId,goodsId,goodsType);
	}
	
	/**
	 * 查询用户历史浏览记录
	 * @param userid
	 * @return
	 */
	public List<History> findHistory(int userid) {
		String sqlSelect="select * from t_bus_user_history where userId=? order by createDate DESC";
		return History.me().find(sqlSelect, userid);
	}
	
	
	
	/**
	 * 查询历史记录普通商品详情
	 * @param goodsId
	 * @return
	 */
	public Record findHistoryDetail(int goodsId) {
		String sql="select tg.id,tg.name,tg.shortinfo,tg.pstate,tg.thumb,tgs.saleprice "
				+ "from t_bus_goods tg "
				+ "left join t_bus_goods_sale tgs on tgs.goodsid=tg.id "
				+ "inner join (select min(saleprice),id from t_bus_goods_sale tgs  where tgs.goodsid=?) tgss on tgs.id=tgss.id ";
		
		return Db.findFirst(sql,goodsId);
	}
	
	/**
	 * 查询历史记录兑换商品详情
	 * @param goodsId
	 * @return
	 */
	public Record findHistoryExchangeDetail(int goodsId) {
		String sql="select tg.id,tg.name,tg.shortinfo,tg.pstate,tg.thumb,tgs.* "
				+ "from t_bus_ege_goods tg left join t_bus_ege_goods_spec tgs on tgs.goodId=tg.id "
				+ "where tgs.id = (select min(id) from t_bus_ege_goods_spec tgs  where tgs.goodId=?)";
		return Db.findFirst(sql,goodsId);
	}
	
	/**
	 * 查询用户积分变化记录
	 * @param userid
	 * @return
	 */
	public List<Record> valueLog(int userid) {
		String sqlSelect = "select * from t_bus_user_value_log where userid=? ORDER BY createDate DESC";
		return Db.find(sqlSelect,userid);
	}
	
	/**
	 * 保存用户反馈
	 * @param record
	 * @return
	 * @throws Exception
	 */
	@Before(Tx.class)
	public int addFeedback(Record record) throws Exception {
		//保存反馈
		if(!Db.save("t_sys_feedback", record)){
			throw new Exception("保存反馈失败");
		}
		return 1;
	}

	/**
	 * 存储意见反馈图片
	 * @param re
	 * @return
	 */
	public boolean saveFeedbackImageByLunBoNum(Record re) {
		return Db.save("t_sys_feedback_images", re);
	}
	
	/**
	 * 刷新token
	 * @param token
	 * @return 
	 */
	public int updateToken(String token) {
		String timestamp = DatetimeKit.getFormatDate(null);
		return Db.update("UPDATE t_api_client_session SET createDate=? WHERE sessionid=?",timestamp,token);
	}
	
	/**
	 * 根据手机号查找
	 * @param phone
	 * @return 
	 */
	public Record findUserByPhone(String phone) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT id FROM t_bus_user WHERE phone = ?",phone);
	}
	
	/**
	 * 签到
	 * @param user 当前用户对象
	 * @param sppoint 签到奖励积分
	 * @param re 添加财富变化记录
	 * @param signSum 签到累计天数
	 * @return
	 * @throws Exception 
	 */
	@Before(Tx.class)
	public boolean sign(User user,Double sppoint,Record re,int signSum,int type) throws Exception {
		//更改签到信息
		user.set("signSum", signSum).set("signRecord", new Date());
		if(update(user)){
			int count = updateValue(user,sppoint,0.00,0.00,0.00,0.00,re,type);
			if (count==1) {
				return true;
			}else {
				return false;
			}
		}else{
			throw new Exception("签到失败");
		}
		
	} 
	
	/**
	 * 保存微信OpenID
	 * @param record
	 * @return
	 */
	public boolean wxOpenId(Record record) {
		return Db.save("t_bus_user_wx", record);
	}
	
	/**
	 * 查询微信用户
	 * @param openId
	 * @return
	 */
	public Record FindWxUser(String openId) {
		return Db.findFirst("select * from t_bus_user_wx where openid=?",openId);
	}
	
	/**
	 * 更新微信用户
	 * @param record
	 * @return
	 * @throws Exception 
	 */
	@Before(Tx.class)
	public boolean updateUserInfoWx(Record record)  {
		
			return Db.update("t_bus_user_wx", record);
		
	}
	/**
	 * 微信用户登录
	 * @param record
	 * @return
	 * @throws Exception 
	 */
	@Before(Tx.class)
	public boolean wxLogin(Record record,String sessionid,int userid,String clientid) throws Exception {
		if (Db.update("t_bus_user_wx", record)) {
			if (!login(sessionid, userid, clientid)) {
				throw new Exception("微信登录失败");
			}else {
				return true;
			}
		}else {
			return false;
		}
	}
	/**
	 * 查询当前用户惠券转换余额的最新记录
	 * @param userId
	 * @return
	 */
	public Record queryExchangeMoneyState(int userId) {
		return Db.findFirst("select * from t_bus_user_changeToMoney where userId= ? order by createTime DESC",userId);
	}
	
	/**
	 * 惠券转换余额
	 * @param user
	 * @param jypoint
	 * @param money
	 * @param re
	 * @return
	 * @throws Exception
	 */
	@Before(Tx.class)
	public boolean exchangeMoney(User user,Double jypoint,Double money,String orderNumStore,Record re,int type) throws Exception {
		//更改信息
		Record record=new Record();
		record.set("money", money).set("jypoint", jypoint).set("state", 1).set("userId", user.getInt("id")).set("orderNo", orderNumStore);
		if(Db.save("t_bus_user_changeToMoney", record)){
			int count = updateValue(user,0.00,0.00,jypoint,0.00,money,re,type);
			if (count==1) {
				return true;
			}else {
				return false;
			}
		}else{
			throw new Exception("转换失败");
		}
		
	} 
	
	/**
	 *查询最近七天内变动惠券
	 * @param createDate
	 * @return
	 */
	public Record queryViablejypoint(int userId,Date createDate) {
		String sql="select IFNULL(SUM(coupon),0)  as viablejypoint from t_bus_user_value_log b  "
				+ "where b.createDate > ? and b.userid=? and type=1 "
				+ "and  (fromOrderNum not in "
					+ "(select fromOrderNum from t_bus_user_value_log where createDate > ? and userid =? and flag in (16,17)) "
					+ " or flag=19) "
				+ " and flag!=13";
		return Db.findFirst(sql, createDate,userId,createDate,userId);
	} 
	
	/**
	 * 查询用户惠券转余额记录
	 * @param userid
	 * @return
	 */
	public List<Record> couponToMoneyLog(int userid) {
		String sqlSelect = "select * from t_bus_user_changeToMoney where userid=? ORDER BY createTime DESC";
		return Db.find(sqlSelect,userid);
	}
	
	/**
	 * 查询未领取奖励的充值订单
	 * @param userId
	 * @return
	 */
	public List<Record> queryRechargeOrder(int userId) {
		return Db.find("select * from t_bus_order_apirecord where userId = ? and state = 0",userId);
	}
	
	
	/**
	 * 充值奖励发放
	 * @param orderNum
	 * @param user
	 * @param ra
	 * @param re
	 * @return
	 * @throws Exception
	 */
	@Before(Tx.class)
	public boolean rechargeAward(String orderNum,User user,Double ra,Record re,int type) throws Exception {
		Record record= Db.findFirst("select * from t_bus_order_apirecord where orderNum = ? and state = 0",orderNum);
		if (record!=null) {
			Double spoint=ra*record.getDouble("amount");
			if (updateValue(user, spoint, 0.00, 0.00, 0.00, 0.00, re,type)==1) {
				record.set("state", 1);
				if (Db.update("t_bus_order_apirecord", record)) {
					return true;
				}else {
					return false;
				}
			}else {
				return false;
			}
		}else {
			return true;
		}
	}

	/**
	 * 查找团购免单订单
	 * @param user
	 * @return 
	 */
	public List<OrderG> freeOrder(User user) {
		String sql = "SELECT g.*,tg.name,tg.thumb FROM t_bus_order_gb g "
				+ " LEFT JOIN t_bus_goods tg ON g.goodsid=tg.id "
				+ " WHERE g.userid=? AND g.isFree=? ";
		return OrderG.me().find(sql,user.getInt("id"),GroupCode.isFree.BACKED);
	}
	
	/**
	 * 查询用户充值记录
	 * @param userid 用户ID
	 * @return
	 */
	public List<OrderZ> queryMoneyIn1(int userid,String type) {
		String sql ="SELECT o.*,m.nowAmount "
				+ " FROM t_bus_order_ecard o "
				+ " LEFT JOIN t_bus_user_money m ON o.orderNum=m.fromOrderNum "
				+ " WHERE o.userid=? ";
		if ( type.equals("1") ){
			//充值
			sql+=" AND o.paytype!=6 ORDER BY o.startDate DESC ";
		}else {
			sql+=" AND o.paytype=6 ORDER BY o.startDate DESC ";
		}
		return OrderZ.me().find(sql,userid);
	}
	
	/**
	 * 查询用户余额记录
	 * @param userid 用户ID
	 * @return
	 */
	public List<Record> queryMoneyRecord(int userid) {
		return Db.find("SELECT * FROM  t_bus_user_money  WHERE userid=? ORDER BY id DESC",userid);
	}
	/**
	 * 提现
	 * @param user
	 * @param money
	 * @return -1 余额不足  -2 保存用户失败
	 */
	@Before(Tx.class)
	public void giveOut(User user, String money) throws Exception{
		// TODO Auto-generated method stub
		//提现金额
		Double price = Double.parseDouble(money);
		//用户余额
		Double oldmoney = user.getDouble("money");
		if ( price.compareTo(oldmoney)>0 ){
			throw new RuntimeException("余额不足");
			//余额不足
		}
		//扣除用户余额
		double result = DoubleKit.sub(oldmoney, price);
		user.set("money", result);
		if (!user.update()){
			throw new RuntimeException("修改用户余额失败");
		}
		String orderNum = "TX"+DatetimeKit.getFormatDate("yyyyMMddHHmmssSSS")+CommonKit.randomNum(2);
		//生成日志
		Record rec = new Record()
				.set("userid", user.getInt("id"))
				.set("amount", price)
				.set("type", UserCode.UserLevelLogType.TWO)
				.set("describe", "提现申请")
				.set("fromOrderNum", orderNum)
				.set("createDate", DatetimeKit.getFormatDate(null))
				.set("orderType", 3)
				.set("nowAmount", result)
				.set("oldAmount", oldmoney);
		if ( !Db.save("t_bus_user_money", rec)  ){
			throw new RuntimeException("记录用户余额变更失败t_bus_user_money");
		}
		Record re = new Record()
				.set("orderNum", orderNum)
				.set("paytype", 6)
				.set("userid", user.getInt("id"))
				.set("amount", price)
				.set("startDate", DatetimeKit.getFormatDate(null))
				.set("outStarte", 1);
		if ( !Db.save("t_bus_order_ecard", re) ) throw new RuntimeException("生成提现订单失败");
		
	}
	/*
	 * 为你推荐
	 * @param user
	 */
	public List<Record> recommend(User user) {
		List<Record> list = null;
		StringBuffer sb = null;
		if(user != null){
			sb = new StringBuffer();
			sb.append("SELECT distinct ");
			sb.append("ftg.id, ");
			sb.append("ftg.thumb, ");
			sb.append("ftg.name, ");
			sb.append("ftg.sellPrice, ");
			sb.append("ftg.shortInfo, ");
			sb.append("ftg.browse, ");
			sb.append("ftgs.saleprice ");
			sb.append("FROM ");
			sb.append("t_bus_goods tg ");
			sb.append("RIGHT JOIN t_bus_user_history th ON (tg.id = th.goodsId AND th.goodsType=1 AND userId=?) ");
			sb.append("LEFT JOIN t_bus_goods ftg ON (ftg.categorythid = tg.categorythid) ");
			sb.append("LEFT JOIN t_bus_goods_sale ftgs ON (ftg.id = ftgs.goodsid) ");
			sb.append("WHERE 1=1 AND tg.state=2 AND tg.groupbuying=1 ");
			sb.append("GROUP BY th.goodsId  ORDER BY ftg.browse desc limit 10");
			list = Db.find(sb.toString(),user.getInt("id"));
		}
		if( list == null || list.size()==0 ){
			sb = new StringBuffer();
			sb.append("SELECT ");
			sb.append("tg.id, ");
			sb.append("tg.thumb, ");
			sb.append("tg.name, ");
			sb.append("tg.sellPrice, ");
			sb.append("tg.shortInfo, ");
			sb.append("ftgs.saleprice, ");
			sb.append("tg.browse ");
			sb.append("FROM ");
			sb.append("t_bus_goods tg ");
			sb.append("LEFT JOIN t_bus_goods_sale ftgs ON (tg.id = ftgs.goodsid) ");
			sb.append("WHERE ");
			sb.append("tg.state=?  AND tg.groupbuying=1  ");
			sb.append("GROUP BY ftgs.goodsId  ORDER BY tg.browse desc limit 10");
			list = Db.find(sb.toString(),2);
		}
		return list;
		
		
	}
	
	
}
